package com.scalar.chiptrack.operations.reports.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.operations.reports.DataDumpReportsHelper;
import com.scalar.chiptrack.operations.reports.WaferStartData;
import com.scalar.chiptrack.operations.reports.forms.TransactionReportForm;
import com.scalar.chiptrack.operations.dataentry.rmaReceive.forms.RMAReceiveForm;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Map;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Workbook;


/**
 * Created by Veeru.
 * Date: Oct 18, 2005
 * Time: 7:06:36 PM
 */

public class DataDumpReportsDAO
{

    public static ArrayList getSortReport ( DBTransaction dbTransaction, String fromDate, String toDate ) throws ScalarException
    {
        Connection conn     = null;
        PreparedStatement pstmt = null;
        ResultSet rset      = null;
        boolean success     = false;

        ArrayList lotInfoList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( SqlQueryMappings.get ( "operations.reports.datadump.sort.report" ) );
            pstmt.setDate( 1, StringUtils.getSQLDate( fromDate ) );
            pstmt.setDate( 2, StringUtils.getSQLDate( toDate ) );
            rset    = pstmt.executeQuery();

            lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            //e.printStackTrace();
            throw new ScalarException ( "Could not load backlog records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }

    public static ArrayList getAssemblyReport ( DBTransaction dbTransaction, String fromDate, String toDate ) throws ScalarException
    {
        Connection conn     = null;
        PreparedStatement pstmt = null;
        ResultSet rset      = null;
        boolean success     = false;

        ArrayList lotInfoList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( SqlQueryMappings.get ( "operations.reports.datadump.assembly.report" ) );
            pstmt.setDate( 1, StringUtils.getSQLDate( fromDate ) );
            pstmt.setDate( 2, StringUtils.getSQLDate( toDate ) );
            rset    = pstmt.executeQuery();

            lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load backlog records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }
    public static ArrayList getTransactionReport ( DBTransaction dbTransaction, String fromDate, String toDate ) throws ScalarException
    {
        Connection conn     = null;
        PreparedStatement pstmt = null;
        ResultSet rset      = null;
        boolean success     = false;

        ArrayList lotInfoList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( SqlQueryMappings.get ( "operations.reports.datadump.transaction.report" ) );
            pstmt.setDate( 1, StringUtils.getSQLDate( fromDate ) );
            pstmt.setDate( 2, StringUtils.getSQLDate( toDate ) );
            rset    = pstmt.executeQuery();

            lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load backlog records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }
    public static ArrayList getSortTransactionDataDumpReport ( DBTransaction dbTransaction, String fromDate, String toDate ) throws ScalarException
    {
        Connection conn     = null;
        PreparedStatement pstmt = null;
        ResultSet rset      = null;
        boolean success     = false;

        ArrayList lotInfoList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( SqlQueryMappings.get ( "operations.reports.datadump.sort.transaction.report" ) );
            pstmt.setDate( 1, StringUtils.getSQLDate( fromDate ) );
            pstmt.setDate( 2, StringUtils.getSQLDate( toDate ) );
            rset    = pstmt.executeQuery();

            lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Sort Transaction Data Dump report details", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }
    public static ArrayList getAssemblyTransactionDataDumpReport ( DBTransaction dbTransaction, String fromDate, String toDate ) throws ScalarException
    {
        Connection conn     = null;
        PreparedStatement pstmt = null;
        ResultSet rset      = null;
        boolean success     = false;

        ArrayList lotInfoList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( SqlQueryMappings.get ( "operations.reports.datadump.assembly.transaction.report" ) );
            pstmt.setDate( 1, StringUtils.getSQLDate( fromDate ) );
            pstmt.setDate( 2, StringUtils.getSQLDate( toDate ) );
            rset    = pstmt.executeQuery();

            lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Assembly Transaction Data Dump report details", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }

    public static ArrayList getInventoryCostReport ( DBTransaction dbTransaction, int baseProductId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList lotInfoList   = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_INVENTORY_SUMMARY_COST_REPORT( ?, '0', '0' ) }" );
            pstmt.setInt( 1, baseProductId );
            rset    = pstmt.executeQuery();

            lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load invetory cost records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }

    public static ArrayList getWipInventoryInfoReport ( DBTransaction dbTransaction, String processType, String psIds, String vendor, String lotCategory, String asOfDate, String status, String reportType, String searchPartNo, String defaultPrompt ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList lotInfoList   = new ArrayList();


        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            //processType, psIds, vendor, lotCategory, asOfDate, status, reportType, searchPartNo;
            pstmt   = conn.prepareStatement ( "{ CALL sp_wip_info_data_dump_report_latest( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            pstmt.setString( 1, processType );
            pstmt.setString( 2, psIds );
            pstmt.setString( 3, vendor );
            pstmt.setString( 4, lotCategory );
            pstmt.setString( 5, asOfDate );
            pstmt.setString( 6, status );
            pstmt.setString( 7, reportType );
            pstmt.setString( 8, searchPartNo );
            pstmt.setString( 9, defaultPrompt );

            rset    = pstmt.executeQuery();

           lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load wip inventory info records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }

    public static Workbook getWipInventoryInfoReport ( DBTransaction dbTransaction, Map paramMap, Workbook wb ) throws ScalarException, IOException
    {
        ArrayList lotInfoList = DataDumpReportsDAO.getWipInventoryInfoReport ( dbTransaction,  (String)paramMap.get("processType"),
                                                                                        (String)paramMap.get("psIds"),
                                                                                        (String)paramMap.get("vendor"),
                                                                                        (String)paramMap.get("lotCategory"),
                                                                                        (String)paramMap.get("asOfDate"),
                                                                                        (String)paramMap.get("status"),
                                                                                        (String)paramMap.get("reportType"),
                                                                                        (String)paramMap.get("searchPartNo"), 
                                                                                        (String)paramMap.get("defaultPrompt") );

        return DataDumpReportsHelper.getWorkbook( lotInfoList, "Wip Inventory Info Report", null,null,null, wb );
    }
    public static Workbook getWipInventoryInfoReport ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException, IOException
    {
        return getWipInventoryInfoReport( dbTransaction, paramMap, null);
    }

    public static ArrayList getCreditDebitNoteReportInfo ( DBTransaction dbTransaction, String customerIds, String productIds, String liType, String status, String fromDate, String toDate, String reportType, String defaultPrompt ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList lotInfoList   = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            //processType, psIds, vendor, lotCategory, asOfDate, status, reportType, searchPartNo;
            pstmt   = conn.prepareStatement ( "{ CALL sp_creadit_debit_note_report_proc( ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            pstmt.setString( 1, customerIds );
            pstmt.setString( 2, productIds );
            pstmt.setString( 3, liType );
            pstmt.setString( 4, status );
            pstmt.setString( 5, fromDate );
            pstmt.setString( 6, toDate );
            pstmt.setString( 7, reportType );
            pstmt.setString( 8, defaultPrompt );

            rset    = pstmt.executeQuery();

            lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Credit/Debit Note Report records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }

    public static ArrayList getCostCodesInfoReport ( DBTransaction dbTransaction, String year, String quater ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList lotInfoList   = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL sp_cost_code_data_dump_report( ?, ? ) }" );
            pstmt.setString( 1, year );
            pstmt.setString( 2, quater );

            rset    = pstmt.executeQuery();

            lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load cost Code info records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }

    public static ArrayList getCostCodeVarianceReport ( DBTransaction dbTransaction, String date1, String date2, String defaultPrompt  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;
       
        ArrayList costVarianceList  = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL sp_cost_variance_report_proc( ?, ?, ? ) }" );
            pstmt.setString( 1, date1 );
            pstmt.setString( 2, date2 );
            pstmt.setString( 3, defaultPrompt );

            rset    = pstmt.executeQuery();

            costVarianceList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load cost Code Variance records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return costVarianceList;
    }

    public static ArrayList getDiscrepancyRecords ( DBTransaction dbTransaction, String processType,String processstepIds,String vendor,String asofdate,String searchPartNo,String reportFormat,String defaultPrompt  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList discrepancyList   = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            //processType, psIds, vendor, lotCategory, asOfDate, status, reportType, searchPartNo;
            pstmt   = conn.prepareStatement ( "{ CALL sp_load_all_lot_qty_discrepancy_reports( ?, ?, ?, ?, ?, ?, ? ) }" );
            pstmt.setString( 1, processType );
            pstmt.setString( 2, processstepIds );
            pstmt.setString( 3, vendor );
            pstmt.setString( 4, asofdate );
            pstmt.setString( 5, searchPartNo );
            pstmt.setString( 6, reportFormat );
            pstmt.setString( 7, defaultPrompt );

            rset    = pstmt.executeQuery();

            discrepancyList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Discrepancy Records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return discrepancyList;
    }

    public static ArrayList getCummYieldReport ( DBTransaction dbTransaction, int baseProductId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;
        ArrayList lotInfoList   = new ArrayList();


        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL sp_lot_yield_by_lot_number( ? ) }" );
            pstmt.setInt( 1, baseProductId );
            rset    = pstmt.executeQuery();

            lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            success = false;
            e.printStackTrace();
            throw new ScalarException ( "Could not load cumm yield records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }
    public static ArrayList getShipmentRecords ( DBTransaction dbTransaction, String processType, String processstepIds, String baseProductIds, String vendor, String fromDate, String toDate, String searchPartNo, String category, String status, String reportFrom, String reportFormat,String defaultPrompt  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList shipmentList      = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            //processType, processstepIds, baseProductIds, vendor, fromDate, toDate, searchPartNo, category, status, reportFrom, reportFormat, defaultPrompt
            pstmt   = conn.prepareStatement ( "{ CALL sp_transit_report_proc ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );

            pstmt.setString( 1, processType );
            pstmt.setString( 2, processstepIds );
            pstmt.setString( 3, baseProductIds );
            pstmt.setString( 4, vendor );
            pstmt.setString( 5, fromDate );
            pstmt.setString( 6, toDate );
            pstmt.setString( 7, searchPartNo );
            pstmt.setString( 8, category );
            pstmt.setString( 9, status );
            pstmt.setString( 10, reportFrom );
            pstmt.setString( 11, reportFormat );
            pstmt.setString( 12, defaultPrompt );

            rset    = pstmt.executeQuery();

            shipmentList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Shipment Records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return shipmentList;
    }

    public static ArrayList getReceivingReport ( DBTransaction dbTransaction, String processType, String processstepIds, String baseProductIds, String vendor, String fromDate, String toDate, String reportType  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList shipmentList      = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL sp_wip_info_receive_report ( ?, ?, ?, ?, ?, ?, ? ) }" );

            pstmt.setString( 1, processType );
            pstmt.setString( 2, processstepIds );
            pstmt.setString( 3, baseProductIds );
            pstmt.setString( 4, vendor );
            pstmt.setString( 5, fromDate );
            pstmt.setString( 6, toDate );
            pstmt.setString( 7, reportType );
            
            rset    = pstmt.executeQuery();

            shipmentList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Receive Lots Info from Data base", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return shipmentList;
    }

    public static ArrayList getTransferMaterialReport ( DBTransaction dbTransaction, String processType, String processstepIds, String baseProductIds, String vendor, String fromDate, String toDate ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList transferMaterialList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_TRANSFER_MATERIAL_LOT_REPORT ( ?, ?, ?, ?, ?, ? ) }" );

            pstmt.setString( 1, processType );
            pstmt.setString( 2, processstepIds );
            pstmt.setString( 3, baseProductIds );
            pstmt.setString( 4, vendor );
            pstmt.setString( 5, fromDate );
            pstmt.setString( 6, toDate );

            rset    = pstmt.executeQuery();

            transferMaterialList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Transferred Materail Records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return transferMaterialList;
    }

    public static ArrayList getWipVsBacklogPlanningReport ( DBTransaction dbTransaction,  int baseProductId, int processstepId,String chipSet  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList wipVsbacklogList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            
            pstmt   = conn.prepareStatement ( "{ CALL sp_planning_report_detail_by_backLog ( ?, ?, ? ) }" );

            pstmt.setInt( 1, baseProductId );
            pstmt.setInt( 2, processstepId );
            pstmt.setString( 3, chipSet );

            rset    = pstmt.executeQuery();

            wipVsbacklogList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not Wip Vs Backlog Planing report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return wipVsbacklogList;
    }

    public static ArrayList getWipVsBacklogPlanningReportByDate ( DBTransaction dbTransaction,  int baseProductId, int processstepId,String chipSet, String fromDate, String toDate  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList wipVsbacklogList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL sp_planning_report_detail_by_backLog ( ?, ?, ?,?,? ) }" );

            pstmt.setInt( 1, baseProductId );
            pstmt.setInt( 2, processstepId );
            pstmt.setString( 3, chipSet );
            pstmt.setString( 4, fromDate );
            pstmt.setString( 5, toDate );

            rset    = pstmt.executeQuery();

            wipVsbacklogList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not Wip Vs Backlog Planing report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return wipVsbacklogList;
    }

    public static ArrayList getRMAReceiveReport ( DBTransaction dbTransaction,  RMAReceiveForm rmaReceiveReportForm ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList rmaReceiveReportList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_RMA_RECEIVE_REPORT ( ?, ?, ?, ?, ? ) }" );
           
            pstmt.setInt( 1, rmaReceiveReportForm.getCustomerId() );
            pstmt.setInt( 2, rmaReceiveReportForm.getProcessStepId() );
            pstmt.setString( 3, rmaReceiveReportForm.getVendorName() );
            pstmt.setString( 4, rmaReceiveReportForm.getFromDate() );
            pstmt.setString( 5, rmaReceiveReportForm.getToDate() );

            rset    = pstmt.executeQuery();

            rmaReceiveReportList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get RMA Receive report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return rmaReceiveReportList;
    }

    public static ArrayList<WaferStartData> getWaferStartReportWorkbookIkanos( int fabProcessStepId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        WaferStartData waferStartData  = null;
        ArrayList<WaferStartData> waferStartDataList = new ArrayList<WaferStartData>();
        try
        {
            conn    = DataSource.getConnection();
            pstmt   = conn.prepareStatement ( "{ CALL SP_WAFER_START_REPORT_IKANOS( ? ) }" );
            pstmt.setInt( 1, fabProcessStepId );
            rset    = pstmt.executeQuery();

            while ( rset.next() )
            {
            	waferStartData = new WaferStartData();
            	waferStartData.setFabPartNumber( rset.getString ( "FAB_PART_NUMBER") );
            	waferStartData.setFgPartNumber( rset.getString ( "FG_PART_NUMBER") );
            	waferStartData.setChipSet( rset.getString ( "CHIP_SET") );
            	waferStartData.setProductLine( rset.getString ( "Product_Line") );
            	waferStartData.setChipFamily( rset.getString ( "Chip_Family") );
            	waferStartData.setProcess( rset.getString ( "Process") );
            	waferStartData.setCurrent_Qtr_Shipped_Qty( rset.getInt ( "CQ_SHIPPED_QTY" ) );
                waferStartData.setCurrent_Qtr_Forecast( rset.getInt ( "CQ_FORECAST_QTY" ) );
                waferStartData.setQ1_Forecast( rset.getInt ( "Q1_FORECAST_QTY" ) );
                waferStartData.setQ2_Forecast( rset.getInt ( "Q2_FORECAST_QTY" ) );
                waferStartData.setQ3_Forecast( rset.getInt ( "Q3_FORECAST_QTY" ) );
                waferStartData.setCurrent_Qtr_Backlog( rset.getInt ( "CQ_BACKLOG_QTY" ) );
                waferStartData.setFg_min_level_Qty( rset.getInt ( "FG_MIN_LEVEL_QTY" ) );;
                waferStartData.setOpen_PO_Qty( rset.getInt( "FAB_OPEN_PO_QTY" ) );
                waferStartData.setFab_Qty( rset.getInt("FAB_QTY" ) );
                waferStartData.setWafer_Store_Qty( rset.getInt("WAFER_STORE_QTY") );
                waferStartData.setSort_Qty( rset.getInt("SORT_QTY") );
                waferStartData.setDb_Qty( rset.getInt("DIE_BANK_QTY") );
                waferStartData.setAssembly_Qty( rset.getInt("ASSEMBLY_QTY") );
                waferStartData.setPreTest_Qty( rset.getInt("PRE_TEST") );
                waferStartData.setTest_Qty( rset.getInt("TEST_QTY") );
                waferStartData.setEol_Qty( rset.getInt("EOL_QTY") );
                waferStartData.setFg_Qty( rset.getInt("FG_QTY") );
                waferStartData.setFab_Yield( rset.getFloat("FAB_YIELD") );
                waferStartData.setSort_Yield( rset.getFloat("SORT_YIELD") );
                waferStartData.setAssembly_Yield( rset.getFloat("ASSEMBLY_YIELD") );
                waferStartData.setTest_Yield( rset.getFloat("TEST_YIELD") );
                waferStartData.setFabCycleTime( rset.getInt("Fab_CYCLE_TIME") );
                waferStartData.setSortCycleTime( rset.getInt("Sort_CYCLE_TIME") );
                waferStartData.setAssemblyCycleTime( rset.getInt("Assembly_CYCLE_TIME") );
                waferStartData.setTestCycleTime( rset.getInt("TEST_CYCLE_TIME") );
                waferStartData.setEolCycleTime( rset.getInt("EOL_CYCLE_TIME") );
                waferStartData.setGdw( rset.getInt("GROSS_DIE_PER_WAFER") );
                waferStartData.setWaferCost( rset.getFloat( "Wafer_Cost" ) );
                waferStartData.setDiePerPackage( rset.getInt( "DIES_PER_PACKAGE" ) );
                waferStartDataList.add(waferStartData);
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load wafer start report records", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    	waferStartData = new WaferStartData();
    	waferStartData.setFabPartNumber( "  " );
        waferStartDataList.add(waferStartData);
        return waferStartDataList;
        /*Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        Workbook workbook   = null;
        Sheet sheet       = null;

        try
        {
            workbook    = new HSSFWorkbook();
            sheet       = workbook.createSheet( "Wafer Start Report" );
            sheet.setSelected( true );

            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints ( ( short ) 14 );
            titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            titleFont.setFontName( "verdana" );
            titleFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints ( ( short ) 8 );
            headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFont.setFontName( "verdana" );
            headerFont.setColor ( IndexedColors.BLACK.getIndex());

            Font textFont = workbook.createFont();
            textFont.setFontHeightInPoints ( ( short ) 10 );
            textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFont.setFontName( "verdana" );
            textFont.setColor ( IndexedColors.BLACK.getIndex());

            CellStyle titleStyle  = workbook.createCellStyle ( );
            titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
            titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
            titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            titleStyle.setWrapText ( true );
            titleStyle.setFont ( titleFont );

            CellStyle headerStyle  = workbook.createCellStyle ( );
            headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            headerStyle.setWrapText ( true );
            headerStyle.setFont ( headerFont );

            CellStyle productStyle  = workbook.createCellStyle ( );
            productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            productStyle.setWrapText ( true );
            productStyle.setFont ( textFont );

            CellStyle productValueStyle  = workbook.createCellStyle ( );
            productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueStyle.setWrapText ( true );
            productValueStyle.setFont ( textFont );
            productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle percentageStyle  = workbook.createCellStyle ( );
            percentageStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            percentageStyle.setBorderTop ( CellStyle.BORDER_THIN );
            percentageStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            percentageStyle.setBorderRight ( CellStyle.BORDER_THIN );
            percentageStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            percentageStyle.setWrapText ( true );
            percentageStyle.setFont ( textFont );
            percentageStyle.setDataFormat( df.getFormat( "0.00%" ) );

            String currentQtr = getQuarterString( 0 );
            String nextQtr = getQuarterString( 1 );
            String nextNextQtr = getQuarterString( 2 );
            String nextNextNextQtr = getQuarterString( 2 );
           
            String colNames[] = { "Fab Part Number", "Mkt Part Number", "Chip Set", currentQtr +" Shipments", 
                                  currentQtr+" Fct", nextQtr+" Fct", nextNextQtr+" Fct", nextNextNextQtr+" Fct",
                                "Fab Yield", "Sort Yield", "Assembly Yield", "Ft Yield", "GDW",  
                                "Fab Qty", "Wafer Store Qty", "Sort Qty", "Die Bank Qty", "Assembly Qty", "Pre Test Qty", "FT Qty", "EOL Qty", "FG Qty",
                                "Total WIP available", "Min Level Qty"};

            Row row = sheet.createRow( 0 );
            Cell cell = mergeCells ( sheet, 0, (short)0, 0, (short)(colNames.length-1), titleStyle );
            setCellValue ( cell, "Wafer Start Report", titleStyle );
            
            row = sheet.createRow ( 2 );
            for ( int i = 0; i < colNames.length; i++ )
            {
                cell    = row.createCell( (short)i );
                setCellValue ( cell, colNames[i], headerStyle );
                int width = ( colNames[i].length() > 20 ) ? 20 : colNames[i].length();
                sheet.setColumnWidth( (short)i, (short) ( ( ( width + 2 ) * 256 ) ) );
            }


            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_WAFER_START_REPORT( ? ) }" );
            pstmt.setInt( 1, fabProcessStepId );
            rset    = pstmt.executeQuery();

            //waferStartInfoList.add( rowData );
            int nextRowNumber   = 3;
            int nextColumnIdx = 0;
            while ( rset.next() )
            {
                row = sheet.createRow( nextRowNumber++ );
                nextColumnIdx = 0;
                String fabPartNumber= rset.getString ( "FAB_PART_NUMBER");
                String fgPartNumber = rset.getString ( "FG_PART_NUMBER");
                String chipSet		= rset.getString ( "CHIP_SET");
                int cqShippedQty 	= rset.getInt ( "CQ_SHIPPED_QTY" );
                int cqForecastQty 	= rset.getInt ( "CQ_FORECAST_QTY" );
                int nqForecastQty 	= rset.getInt ( "Q1_FORECAST_QTY" );
                int nnqForecastQty 	= rset.getInt ( "Q2_FORECAST_QTY" );
                int nnnqForecastQty = rset.getInt ( "Q3_FORECAST_QTY" );
                int fgMinLevelQty 	= rset.getInt ( "FG_MIN_LEVEL_QTY" );
                int fabQty 			= rset.getInt ( "FAB_QTY" );
                int wsQty 			= rset.getInt ( "WAFER_STORE_QTY" );
                int sortQty 		= rset.getInt ( "SORT_QTY" );
                int dieBankQty 		= rset.getInt ( "DIE_BANK_QTY" );
                int assemblyQty 	= rset.getInt ( "ASSEMBLY_QTY" );
                int preTestQty 		= rset.getInt ( "PRE_TEST" );
                int testQty 		= rset.getInt ( "TEST_QTY" );
                int eolQty 			= rset.getInt ( "EOL_QTY" );
                int fgQty 			= rset.getInt ( "FG_QTY" );
                float fabYield		= rset.getFloat( "FAB_YIELD" );
                float sortYield		= rset.getFloat( "SORT_YIELD" );
                float assemblyYield		= rset.getFloat( "ASSEMBLY_YIELD" );
                float testYield		= rset.getFloat( "TEST_YIELD" );
                int netDiePerWafer 	= rset.getInt ( "GROSS_DIE_PER_WAFER" );

                setCellValue( row, (short)nextColumnIdx++, fabPartNumber, productStyle  );
                setCellValue( row, (short)nextColumnIdx++, fgPartNumber, productStyle );
                setCellValue( row, (short)nextColumnIdx++, chipSet, productStyle );
                setCellValue( row, (short)nextColumnIdx++, cqShippedQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, cqForecastQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, nqForecastQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, nnqForecastQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, nnnqForecastQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, fabYield, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, sortYield, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, assemblyYield, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, testYield, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, netDiePerWafer, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, fabQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, wsQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, sortQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, dieBankQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, assemblyQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, preTestQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, testQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, eolQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, fgQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );

                int totalWipAvailable = (waferQty*netDiePerWafer)+dieBankQty+assemblyQty+testQty+classStoreQty+fgQty;
                //rowData.add ( new DBColumn( new Integer (totalWipAvailable), java.sql.Types.INTEGER ) );
                setCellValue( row, (short)nextColumnIdx++, totalWipAvailable, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //rowData.add ( new DBColumn( new Integer (fgMinLevelQty), java.sql.Types.INTEGER ) );
                setCellValue( row, (short)nextColumnIdx++, fgMinLevelQty, productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //rowData.add ( new DBColumn( new Integer (cqForecastQty+nqForecastQty+fgMinLevelQty-cqShippedQty), java.sql.Types.INTEGER ) );
                setCellValue( row, (short)nextColumnIdx++, (cqForecastQty+nqForecastQty+fgMinLevelQty-cqShippedQty), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //int totalDieRequired = (cqSchBklgQty+fgMinLevelQty)-totalWipAvailable;
                int totalDieRequired = (cqForecastQty+nqForecastQty+fgMinLevelQty-cqShippedQty)-totalWipAvailable;
                //rowData.add ( new DBColumn( new Integer ( totalDieRequired ), java.sql.Types.INTEGER ) );
                setCellValue( row, (short)nextColumnIdx++, (totalDieRequired), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //rowData.add ( new DBColumn( new Integer (netDiePerWafer), java.sql.Types.INTEGER ) );
                setCellValue( row, (short)nextColumnIdx++, (netDiePerWafer), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //rowData.add ( new DBColumn( new Integer (totalDieRequired/netDiePerWafer), java.sql.Types.INTEGER ) );
                setCellValue( row, (short)nextColumnIdx++, (totalDieRequired/netDiePerWafer), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                long weeklyRunRate = Math.round( ((totalDieRequired*1.0d)/netDiePerWafer)/14 );
                //rowData.add ( new DBColumn( new Integer ((int)weeklyRunRate), java.sql.Types.INTEGER ) );
                setCellValue( row, (short)nextColumnIdx++, (weeklyRunRate), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load wafer start report records", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return workbook;*/
    }

    public static String getQuarterString(int qtrNo) throws ScalarException 
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        int currentQtr	= 1;
        String QtrStr = "";
        int currYear = 0;
        Calendar cal = Calendar.getInstance();
        currYear = cal.get( Calendar.YEAR );
        try
        {
            conn    = DataSource.getConnection();
            pstmt   = conn.prepareStatement ( "SELECT CONVERT(INT, SUBSTRING ( CQC_QUARTER,LEN(CQC_QUARTER),1)) FROM Company_Quarterly_Calendar WHERE CQC_START_DATE <= ? AND CQC_END_DATE  >= ?" );
            pstmt.setString( 1, DateUtil.getCurrentDate());
            pstmt.setString( 2, DateUtil.getCurrentDate());
            rset    = pstmt.executeQuery();
            if( rset.next() )
            {
            	currentQtr = rset.getInt( 1 );
            }

            if( qtrNo == 0 )
            {
            	QtrStr = "Q"+currentQtr+ "-" +( (currYear+"").substring( 2 ) );             	
            }
            else
            {
            	if( currentQtr + qtrNo > 4 )
            	{
            		QtrStr = "Q"+((currentQtr + qtrNo)-4)+"-" +(((currYear+1)+"").substring( 2 ));
            	}
            	else
            	{
            		QtrStr = "Q"+(currentQtr+qtrNo)+"-" +((currYear+"").substring( 2 ));
            	}
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load inventory Summary Report records", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
	
		return QtrStr;
	}

	public static ArrayList<ArrayList> getInventorySummaryReportInfo ( DBTransaction dbTransaction, String  productId,  String lotCategory, String vendor, String status, String asOfDate, String defaultPrompt ) throws ScalarException
    {
            Connection conn         = null;
            PreparedStatement pstmt = null;
            ResultSet rset          = null;
            boolean success         = false;

            ArrayList<ArrayList> lotInfoList   = new ArrayList<ArrayList>();

            try
            {
                if ( dbTransaction != null )
                {
                    conn    = dbTransaction.getConnection();
                }
                else
                {
                    conn    = DataSource.getConnection();
                }
                //processType, psIds, vendor, lotCategory, asOfDate, status, reportType, searchPartNo;
                pstmt   = conn.prepareStatement ( "{ CALL sp_inventory_summary_report_proc( ?, ?, ?, ?, ?, ? ) }" );
                pstmt.setString( 1, productId );
                pstmt.setString( 2, lotCategory );
                pstmt.setString( 3, vendor );
                pstmt.setString( 4, status );
                pstmt.setString( 5, asOfDate );
                pstmt.setString( 6, defaultPrompt );

                rset    = pstmt.executeQuery();

                lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

                success = true;
            }
            catch ( Exception e )
            {
                e.printStackTrace();
                throw new ScalarException ( "Could not load inventory Summary Report records", e );
            }
            finally
            {
                if ( dbTransaction == null )
                {
                    if ( conn != null )
                    {
                        try
                        {
                            if ( success )
                            {
                                conn.commit();
                            }
                            else
                            {
                                conn.rollback();
                            }
                        }catch ( SQLException e ){}
                    }
                }
                if ( rset != null )
                {
                    try { rset.close(); }catch ( SQLException ignore ) {}
                }
                if ( pstmt != null )
                {
                    try { pstmt.close(); } catch ( SQLException ignore ) {}
                }
                if ( conn != null )
                {
                    try { conn.close(); } catch ( SQLException ignore ) {}
                }
            }
            return lotInfoList;
        }
    
        public static ArrayList getInventorySummaryReport ( DBTransaction dbTransaction, String  productId,  String lotCategory, String vendor, String status, String asOfDate, String defaultPrompt, String psTypes ) throws ScalarException
        {
            Connection conn         = null;
            PreparedStatement pstmt = null;
            ResultSet rset          = null;
            boolean success         = false;

            ArrayList lotInfoList   = new ArrayList();

            try
            {
                if ( dbTransaction != null )
                {
                    conn    = dbTransaction.getConnection();
                }
                else
                {
                    conn    = DataSource.getConnection();
                }
                //processType, psIds, vendor, lotCategory, asOfDate, status, reportType, searchPartNo;
                pstmt   = conn.prepareStatement ( "{ CALL sp_inventory_summary_report_proc_latest( ?, ?, ?, ?, ?, ? ,? ) }" );
                pstmt.setString( 1, productId );
                pstmt.setString( 2, lotCategory );
                pstmt.setString( 3, vendor );
                pstmt.setString( 4, status );
                pstmt.setString( 5, asOfDate );
                pstmt.setString( 6, defaultPrompt );
                pstmt.setString( 7, psTypes );

                rset    = pstmt.executeQuery();

                //lotInfoList = ExcelUtils.getAllResultSetRows( rset, true );

                lotInfoList = ExcelUtils.getAllEditedResultSetRows( rset, true, psTypes );

                success = true;
            }
            catch ( Exception e )
            {
                e.printStackTrace();
                throw new ScalarException ( "Could not load inventory Summary Report records", e );
            }
            finally
            {
                if ( dbTransaction == null )
                {
                    if ( conn != null )
                    {
                        try
                        {
                            if ( success )
                            {
                                conn.commit();
                            }
                            else
                            {
                                conn.rollback();
                            }
                        }catch ( SQLException e ){}
                    }
                }
                if ( rset != null )
                {
                    try { rset.close(); }catch ( SQLException ignore ) {}
                }
                if ( pstmt != null )
                {
                    try { pstmt.close(); } catch ( SQLException ignore ) {}
                }
                if ( conn != null )
                {
                    try { conn.close(); } catch ( SQLException ignore ) {}
                }
            }
            return lotInfoList;
        }

    public static ArrayList<WaferStartData> getWaferStartReportNew(int fabProcessStepId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        WaferStartData waferStartData = null;
        ArrayList<WaferStartData> waferStartDataList = new ArrayList<WaferStartData>();
        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("{ CALL SP_WAFER_START_REPORT_NEW( ? ) }");
            pstmt.setInt(1, fabProcessStepId);
            rset = pstmt.executeQuery();

            while (rset.next()) {
                waferStartData = new WaferStartData();
                waferStartData.setFabPartNumber(rset.getString("FAB_PART_NUMBER"));
                waferStartData.setFgPartNumber(rset.getString("FG_PART_NUMBER"));
                waferStartData.setChipSet(rset.getString("CHIP_SET"));
                waferStartData.setProductLine(rset.getString("Product_Line"));
                waferStartData.setChipFamily(rset.getString("Chip_Family"));
                waferStartData.setProcess(rset.getString("Process"));
                waferStartData.setCurrent_Qtr_Shipped_Qty(rset.getInt("CQ_SHIPPED_QTY"));
                waferStartData.setCurrent_Qtr_Forecast(rset.getInt("CQ_FORECAST_QTY"));
                waferStartData.setQ1_Forecast(rset.getInt("Q1_FORECAST_QTY"));
                waferStartData.setQ2_Forecast(rset.getInt("Q2_FORECAST_QTY"));
                waferStartData.setQ3_Forecast(rset.getInt("Q3_FORECAST_QTY"));
                waferStartData.setCurrent_Qtr_Backlog(rset.getInt("CQ_BACKLOG_QTY"));
                waferStartData.setFg_min_level_Qty(rset.getInt("FG_MIN_LEVEL_QTY"));
                waferStartData.setOpen_PO_Qty(rset.getInt("FAB_OPEN_PO_QTY"));
                waferStartData.setFab_Qty(rset.getInt("FAB_QTY"));
                waferStartData.setWafer_Store_Qty(rset.getInt("WAFER_STORE_QTY"));
                waferStartData.setSort_Qty(rset.getInt("SORT_QTY"));
                waferStartData.setDb_Qty(rset.getInt("DIE_BANK_QTY"));
                waferStartData.setAssembly_Qty(rset.getInt("ASSEMBLY_QTY"));
                waferStartData.setPreTest_Qty(rset.getInt("PRE_TEST"));
                waferStartData.setTest_Qty(rset.getInt("TEST_QTY"));
                waferStartData.setEol_Qty(rset.getInt("EOL_QTY"));
                waferStartData.setFg_Qty(rset.getInt("FG_QTY"));
                waferStartData.setFab_Yield(rset.getFloat("FAB_YIELD"));
                waferStartData.setSort_Yield(rset.getFloat("SORT_YIELD"));
                waferStartData.setAssembly_Yield(rset.getFloat("ASSEMBLY_YIELD"));
                waferStartData.setTest_Yield(rset.getFloat("TEST_YIELD"));
                waferStartData.setFabCycleTime(rset.getInt("Fab_CYCLE_TIME"));
                waferStartData.setSortCycleTime(rset.getInt("Sort_CYCLE_TIME"));
                waferStartData.setAssemblyCycleTime(rset.getInt("Assembly_CYCLE_TIME"));
                waferStartData.setTestCycleTime(rset.getInt("TEST_CYCLE_TIME"));
                waferStartData.setEolCycleTime(rset.getInt("EOL_CYCLE_TIME"));
                waferStartData.setGdw(rset.getInt("GROSS_DIE_PER_WAFER"));
                waferStartData.setWaferCost(rset.getFloat("Wafer_Cost"));
                waferStartData.setDiePerPackage(rset.getInt("DIES_PER_PACKAGE"));
                waferStartData.setM1_fc_qty(rset.getInt("M1_FORECAST_QTY"));
                waferStartData.setM2_fc_qty(rset.getInt("M2_FORECAST_QTY"));
                waferStartData.setM3_fc_qty(rset.getInt("M3_FORECAST_QTY"));
                waferStartData.setM4_fc_qty(rset.getInt("M4_FORECAST_QTY"));
                waferStartData.setM5_fc_qty(rset.getInt("M5_FORECAST_QTY"));
                waferStartData.setM6_fc_qty(rset.getInt("M6_FORECAST_QTY"));
                waferStartData.setM7_fc_qty(rset.getInt("M7_FORECAST_QTY"));
                waferStartData.setM8_fc_qty(rset.getInt("M8_FORECAST_QTY"));
                waferStartData.setM9_fc_qty(rset.getInt("M9_FORECAST_QTY"));
                waferStartData.setM10_fc_qty(rset.getInt("M10_FORECAST_QTY"));
                waferStartData.setM11_fc_qty(rset.getInt("M11_FORECAST_QTY"));
                waferStartData.setM12_fc_qty(rset.getInt("M12_FORECAST_QTY"));
                waferStartData.setAssemblyPartNumber(rset.getString("ASSEMBLY_PART_NUMBER"));
                waferStartData.setAssemblyReleaseQty(rset.getInt("ASSEMBLY_RELEASE_QTY"));
                waferStartData.setM1_bklg_qty(rset.getInt("M1_BACKLOG_QTY"));
                waferStartData.setM2_bklg_qty(rset.getInt("M2_BACKLOG_QTY"));
                waferStartData.setM3_bklg_qty(rset.getInt("M3_BACKLOG_QTY"));
                waferStartData.setM4_bklg_qty(rset.getInt("M4_BACKLOG_QTY"));
                waferStartData.setM5_bklg_qty(rset.getInt("M5_BACKLOG_QTY"));
                waferStartData.setM6_bklg_qty(rset.getInt("M6_BACKLOG_QTY"));
                waferStartData.setM7_bklg_qty(rset.getInt("M7_BACKLOG_QTY"));
                waferStartData.setM8_bklg_qty(rset.getInt("M8_BACKLOG_QTY"));
                waferStartData.setM9_bklg_qty(rset.getInt("M9_BACKLOG_QTY"));
                waferStartData.setM10_bklg_qty(rset.getInt("M10_BACKLOG_QTY"));
                waferStartData.setM11_bklg_qty(rset.getInt("M11_BACKLOG_QTY"));
                waferStartData.setM12_bklg_qty(rset.getInt("M12_BACKLOG_QTY"));

                if(StringUtils.isNullOrEmpty(waferStartData.getFabPartNumber())) {
                    waferStartData.setFabPartNumber(waferStartData.getFgPartNumber());
                }

                waferStartDataList.add(waferStartData);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Could not load wafer start report records", e);
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        waferStartData = new WaferStartData();
        waferStartData.setFabPartNumber("  ");
        waferStartData.setAssemblyPartNumber("  ");
        waferStartDataList.add(waferStartData);
        return waferStartDataList;
    }

    public static ArrayList getOpenPOPPVIPVReports ( DBTransaction dbTransaction, String reportType  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList openPoList      = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            if ("openPO".equalsIgnoreCase( reportType ) )
            {
                pstmt   = conn.prepareStatement ( "{ CALL SP_LOAD_OPEN_PO_REPORT ( ? ) }" );
            }
            else
            {
                pstmt   = conn.prepareStatement ( "{ CALL SP_LOAD_OPEN_PO_PPV_IPV_REPORT ( ? ) }" );
            }

            pstmt.setInt( 1, 0 );

            rset    = pstmt.executeQuery();

            openPoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Open Po Report Records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return openPoList;
    }

    public static ArrayList getSiTimeTransactionReports ( DBTransaction dbTransaction, TransactionReportForm transReportForm  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList openPoList      = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            if ("transaction".equalsIgnoreCase( transReportForm.getReportType() ) )
            {
                pstmt   = conn.prepareStatement ( "{ CALL SP_TRANSACTION_REPORT_SITIME ( ?,?,?,?,?,?,? ) }" );
            }
            else
            {
                pstmt   = conn.prepareStatement ( "{ CALL SP_VALUEADD_YIELD_REPORT ( ?,?,?,?,?,?,? ) }" );
            }

            pstmt.setInt( 1, transReportForm.getBaseProductId() );
            pstmt.setString( 2, transReportForm.getFromDate() );
            pstmt.setString( 3, transReportForm.getToDate() );
            pstmt.setString( 4, transReportForm.getProcessType() );
            pstmt.setString( 5, transReportForm.getVendorName() );
            pstmt.setInt( 6, transReportForm.getProcessStepId() );
            pstmt.setString( 7, transReportForm.getReportType() );

            rset    = pstmt.executeQuery();

            openPoList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Transaction Report Records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return openPoList;
    }

}

